﻿using System;
using System.Collections.Generic;
using System.Collections;
using System.Text;
using System.Data;

namespace ModelLibrary
{
    public class DeviceInfo : clsBaseLib
    {
        public string GlobleErrString = string.Empty;
        public DeviceInfo()
        {
            base.connectDataBase();
        }
        public DeviceInfo(string sAppSet)
        {
            base.connectDataBase(sAppSet);
        }
        #region //设备变更
        public DataSet getDevChangeInfoById(int iChangeId)
        {
            string sSqlString = @"SELECT     b.DevID, b.ChangeDateTime, b.ChangeStatus, b.ChangeComment, a.DeviceCode, a.DeviceName
FROM         dbo.Device AS a INNER JOIN
                      dbo.Table_DevChangeList AS b ON a.DeviceId = b.DevID where b.ID='" + iChangeId + "'";
            DataSet ds = db.GetDataSetBySql(sSqlString);
            if (ds == null)
            {
                GlobleClass.sGlobleErrorString = db.GlobleErrString;
            }
            return ds;
        }

        public bool setStateChangeInfo(int iDevId, string sDateTime, int iState, string sComment)
        {
            string sSql = string.Format(@"INSERT INTO Table_DevChangeList(DevID,ChangeDateTime,ChangeStatus,ChangeComment)
     VALUES({0},'{1}',{2},'{3}') 
Update Device as d set d.DeviceState={2} where d.ID={0}"
                , iDevId, sDateTime, iState, sComment);
            
            return db.ExecuteNonQuery(sSql, true) > 0;
        }

        public DataTable getResultDevChangeInfo(int iIds)
        {
            string sSql = string.Format(@"SELECT     b.BuyPrice, a.ID, a.DevID, a.ChangeDateTime, a.ChangeStatus, a.ChangeComment, b.DeviceCode, b.DeviceName, c.EnumName
FROM         dbo.Device AS b INNER JOIN
                      dbo.Table_DevChangeList AS a ON b.DeviceId = a.DevID INNER JOIN
                          (SELECT     EnumId, EnumTypeId, EnumName, EnumDesc, EnumTypeName
                            FROM          dbo.View_EnumId
                            WHERE      (EnumTypeId = 3)) AS c ON a.ChangeStatus = c.EnumId
        where a.DevID={0}", iIds);
            return db.GetTable(sSql);
        }

        #endregion

        #region  //设备调拨
        public DataSet getDevMoveInfoById(int iMoveId)
        {
            string sSqlString = string.Format(@"SELECT     a.Id, a.DiaoboID, a.BeforDeptId, b.DeptName, a.AfterDeptId,
                          (SELECT     DeptName
                            FROM          dbo.Department AS c
                            WHERE      (a.AfterDeptId = DeptId)) AS AfDeptName, a.dProcessTime, a.DiaoboStatus, a.DiaoboReason
FROM         dbo.Table_DiaoboNote AS a INNER JOIN
                      dbo.Department AS b ON a.BeforDeptId = b.DeptId where a.ID={0}
            SELECT     a.DeviceCode, a.DeviceName, a.BuyPrice
FROM         dbo.Device AS a INNER JOIN
                      dbo.Table_DiaoboList AS b ON a.DeviceId = b.DevID
        where b.DiaoboID ={0}", iMoveId);
            DataSet ds = db.GetDataSetBySql(sSqlString);
            if (ds == null)
            {
                GlobleClass.sGlobleErrorString = db.GlobleErrString;
            }
            return ds;
        }
        public DataSet GetStateInfo()
        {
            string sSqlString = @"SELECT     *
FROM         Table_DevChangeList";
            DataSet ds = db.GetDataSetBySql(sSqlString);
            if (ds == null)
            {
                GlobleClass.sGlobleErrorString = db.GlobleErrString;
            }
            return ds;
        }

        public bool setMoveInfo(int imid, string smcode, string smdate, int imbfid, int imafid, string smreason, string smstate, string sFiles, string[] sDevices)
        {
            string sSql = imid == 0 ? string.Format(@"INSERT INTO Table_DiaoboNote(DiaoboID,BeforDeptId,AfterDeptId,dProcessTime,DiaoboStatus,DiaoboReason)
     VALUES('{0}',{1},{2},'{3}','{4}','{5}')  declare @Id int set @Id=@@identity "
                , smcode, imbfid, imafid, smdate, smstate, smreason)
                : string.Format(@"UPDATE Table_DiaoboNote SET DiaoboID = '{0}',BeforDeptId = {1},AfterDeptId = {2},dProcessTime = '{3}',DiaoboStatus = '{4}',DiaoboReason = '{5}' where ID='{6}'"
                , smcode, imbfid, imafid, smdate, smstate, smreason,imid);
            int iLength = sDevices.Length;
            string sContractId = imid > 0 ? Convert.ToString(imid) : "@Id";
            for (int i = 0; i < iLength; i = i + 4)
            {
                sSql += string.Format(@"INSERT INTO Table_DiaoboList(DiaoboID,DevID) VALUES({0},{1}) Update Device as d set d.DeptId={1} where d.ID={2}", imid, sDevices[i],imafid);
            }
            return db.ExecuteNonQuery(sSql, true) > 0;
        }
        
        public DataTable getResultDeviceMoviInfo(string sIds)
        {
            string sSql = string.Format(@"SELECT     b.DeviceCode, b.DeviceName, c.DeptName, b.BuyPrice
FROM         dbo.Table_DiaoboList AS a INNER JOIN
                      dbo.Device AS b ON a.DevID = b.DeviceId INNER JOIN
                      dbo.Department AS c ON b.DeptId = c.DeptId
        where a.DiaoboID='{0}'", sIds);
            return db.GetTable(sSql);
        }
        #endregion

        #region //备件

        public DataTable getDevPartList(string sDevCode, int iPageId, int iDeptId, string sWhere)
        {
            //int iStartRow = (iPageId - 1) * iPageRowNum + 1;
            //int iEndRow = iPageId * iPageRowNum;
            //string sDept = iDeptId == 0 ? @"" : string.Format(" and c.Deptid in(select deptid from department where dbo.getDeptFullPath(deptid) like dbo.getDeptFullPath({0})+'%')", iDeptId);
            sWhere = sWhere == "" ? "" : "and " + sWhere;
            sWhere = sDevCode == "" ? sWhere : " and ApplyDevices like '%," + sDevCode + ",%' ";
            string sSql = string.Format(@"SELECT * FROM Table_PartList
            where 1=1 {0}", sWhere);
            return db.GetTable(sSql);
        }

        public bool setDevPartInfo(int ipartid, string spartcode, string spartname, string spartdanwei, string spartspec, string spartweight, string spartcailiao, string spartfactory, string spartprice, string spartapplydev, string spartcomment, string sfiles)
        {
            string sSql = ipartid == 0 ? string.Format(@"INSERT INTO Table_PartList
(PartCode,PartName,Jiliangdw,Xinghaogg,SingleWeight,Caizhi,Creater,SinglePrice,ApplyDevices,Comment) VALUES('{0}',{1},{2},'{3}','{4}','{5}','{6}','{7}','{8}','{9}')"
                , spartcode, spartname, spartdanwei, spartspec, spartweight, spartcailiao, spartfactory, spartprice, spartapplydev, spartcomment)
                : string.Format(@"UPDATE Table_PartList SET PartCode = '{0}',PartName = '{1}',Jiliangdw = '{2}',Xinghaogg='{3}',SingleWeight = '{4}',Caizhi = '{5}',Creater = {6},SinglePrice={7},ApplyDevices='{8}',Comment='{9}' where ID={10}"
                , spartcode, spartname, spartdanwei, spartspec, spartweight, spartcailiao, spartfactory, spartprice, spartapplydev, spartcomment, ipartid);
           
            return db.ExecuteNonQuery(sSql, true) > 0;
        }

        public DataSet getDevPartInfoById(int m_Id)
        {
            string sSqlString = @"SELECT     a.*, b.CompanyName
FROM         dbo.Table_PartList AS a INNER JOIN
                      dbo.Company AS b ON a.Creater = b.CompanyId where a.ID='" + m_Id + "'";
            DataSet ds = db.GetDataSetBySql(sSqlString);
            if (ds == null)
            {
                GlobleClass.sGlobleErrorString = db.GlobleErrString;
            }
            return ds;
        }
        public bool deleteDevicePartByIds(string sIds)
        {
            string sSql = string.Format(@"delete from Table_PartList where ID in({0})", sIds);
            return db.ExecuteNonQuery(sSql, true) > 0;
        }


        public DataTable getResultDevPartInfo(string sIds)
        {
            string sSql = string.Format(@"SELECT * from Table_PartList where ID in({0})", sIds);
            return db.GetTable(sSql);
        }
        #endregion

        #region //设备维修申请列表

        public DataTable getRespairAppList11(int iPageId, int iDeptId, string sWhere)
        {
            int iStartRow = (iPageId - 1) * iPageRowNum + 1;
            int iEndRow = iPageId * iPageRowNum;
            string sDept = iDeptId == 0 ? @"" : string.Format(" and c.Deptid in(select deptid from department where dbo.getDeptFullPath(deptid) like dbo.getDeptFullPath({0})+'%')", iDeptId);
            sWhere = sWhere == "" ? "" : "and " + sWhere;
            string sSql = string.Format(@"select * from
            (
            select row_number() over(order by a.ID desc) as rowindex,  c.DeviceCode, c.DeviceName, c.Devicespec, c.DeptId, a.*
FROM         dbo.Device AS c INNER JOIN
                      dbo.Department AS b ON c.DeptId = b.DeptId INNER JOIN
                      dbo.T_RepairPlanAppNote AS a ON c.DeviceId = a.DevID
            where 1=1 {0}
            {1}
            ) t
            where rowindex between {2} and {3}", sDept, sWhere, iStartRow, iEndRow);
            return db.GetTable(sSql);
        }

        public DataTable getRespairAppList(int iPageId, int iDeptId, string sWhere)
        {
            int iStartRow = (iPageId - 1) * iPageRowNum + 1;
            int iEndRow = iPageId * iPageRowNum;
            string sDept = iDeptId == 0 ? @"" : string.Format(" and b.Deptid in(select deptid from department where dbo.getDeptFullPath(deptid) like dbo.getDeptFullPath({0})+'%')", iDeptId);
            sWhere = sWhere == "" ? "" : "and " + sWhere;
            string sSql = string.Format(@"SELECT     a.ID, a.PlanCode, a.DevID, a.PlanType, CONVERT(Varchar(10), a.dCreateTime, 120) AS dCreateTime, CONVERT(Varchar(10), a.dPlanBeginTime, 120) 
                      AS dPlanBeginTime, CONVERT(Varchar(10), a.dPlanEndTime, 120) AS dPlanEndTime, a.RepairNeed, a.bPlanFlag, a.comment, b.DeviceCode, 
                      b.DeviceName, b.Devicespec,
                          (SELECT     DeptName
                            FROM          dbo.Department AS c
                            WHERE      (DeptId = b.DeptId)) AS DeptName
FROM         dbo.T_RepairPlanAppNote AS a INNER JOIN
                      dbo.Device AS b ON a.DevID = b.DeviceId
            where 1=1 {0} {1}", sWhere, sDept, iStartRow, iEndRow);
            return db.GetTable(sSql);
        }

        public int getRepairAppCount(int iDeptId, string sWhere)
        {
            string sDept = iDeptId == 0 ? @"" : string.Format(" and c.deptid in(select DeptId from department where dbo.getDeptFullPath(deptid) like dbo.getDeptFullPath({0})+'%')", iDeptId);
            sWhere = sWhere == "" ? "" : "and " + sWhere;
            string sSql = string.Format(@"select count(ID)
            FROM         dbo.Device AS c INNER JOIN
                      dbo.Department AS b ON c.DeptId = b.DeptId INNER JOIN
                      dbo.T_RepairPlanAppNote AS a ON c.DeviceId = a.DevID
                where 1=1{0}{1}", sDept, sWhere);
            return Convert.ToInt32(db.ExecuteScalar(sSql));
        }

        public bool deleteRepairAppByIds(string sIds)
        {
            string sSql = string.Format(@"delete from T_RepairPlanAppNote where ID in({0}) 
            delete from T_RepairPlanItem where PlanID in({0}) delete from T_RepairPlanPartList where PlanID in({0})", sIds);
            return db.ExecuteNonQuery(sSql, true) > 0;
        }

        public DataSet getRepairPlanAppInfoById(int iAppId)
        {
            string sSql = string.Format(@"SELECT     a.ID, a.PlanCode, a.DevID, a.PlanType, CONVERT(Varchar(10), a.dCreateTime, 120) AS dCreateTime, CONVERT(Varchar(10), a.dPlanBeginTime, 120) AS dPlanBeginTime, 
                      CONVERT(Varchar(10), a.dPlanBeginTime, 120) AS dPlanBeginTime, a.RepairNeed, a.bPlanFlag, a.comment, b.DeviceCode, b.DeviceName, b.Devicespec,
                          (SELECT     DeptName
                            FROM          dbo.Department AS c
                            WHERE      (DeptId = b.DeptId)) AS DeptName
FROM         dbo.T_RepairPlanAppNote AS a INNER JOIN
                      dbo.Device AS b ON a.DevID = b.DeviceId
            where ID={0}
            select  * from T_RepairPlanItem where PlanID={0}
            SELECT     a.ID, a.PlanID, a.PartID, a.iNeedNum, a.dPlanTime, b.PartCode, b.PartName, b.Xinghaogg
FROM         dbo.T_RepairPlanPartList AS a INNER JOIN
                      dbo.Table_PartList AS b ON a.PartID = b.ID 
            where PlanID={0}", iAppId);
            return db.GetDataSetBySql(sSql);
        }

        public bool setRepairAppInfo(int id, string dcode, int idevid, string createdate, string BeginDate, string EndDate, string Need, string[] Items, string[] parts)
        {
            string sSql = id == 0 ? string.Format(@"INSERT INTO T_RepairPlanAppNote(PlanCode,DevID,PlanType,dCreateTime,dPlanBeginTime,dPlanEndTime,RepairNeed)values('{0}',{1},'维修','{2}','{3}','{4}','{5}')  declare @Id int set @Id=@@identity "
                , dcode, idevid, createdate, BeginDate, EndDate, Need)
                : string.Format(@" update T_RepairPlanAppNote set PlanCode ='{0}',DevID = {1},dCreateTime = '{2}',dPlanBeginTime ='{3}',dPlanEndTime ='{4}',RepairNeed ='{5}' where ID={6} "
                , dcode, idevid, createdate, BeginDate, EndDate, Need,id);
            int iLength = Items.Length;
            string sPlanId = id > 0 ? Convert.ToString(id) : "@Id";
            sSql += string.Format(@" delete T_RepairPlanItem where PlanID={0} ",sPlanId);
            for (int i = 0; i < iLength; i = i + 3)
            {
                sSql += string.Format(@" INSERT INTO T_RepairPlanItem (PlanID,ItemName,ItemNeed,dPlanTime,comment) values({0},'{1}','{2}','{3}','{4}') "
                , sPlanId, Items[i], Items[i + 1], createdate, Items[i + 2]);
            }

            iLength = parts.Length;
            sSql += string.Format(@" delete T_RepairPlanPartList where PlanID={0} ", sPlanId);

            for (int j = 0; j < iLength; j = j + 2)
            {
                sSql += string.Format(@" INSERT INTO T_RepairPlanPartList (PlanID,PartID,iNeedNum,dPlanTime) values({0},{1},{2},'{3}') "
                , sPlanId, parts[j], parts[j + 1], createdate);
            }
            return db.ExecuteNonQuery(sSql, true) > 0;
        }

        #endregion

        #region //设备保修列表

        public DataTable getMaintainList(int iPageId, int iDeptId, string sWhere)
        {
            int iStartRow = (iPageId - 1) * iPageRowNum + 1;
            int iEndRow = iPageId * iPageRowNum;
            string sDept = iDeptId == 0 ? @"" : string.Format(" and b.Deptid in(select deptid from department where dbo.getDeptFullPath(deptid) like dbo.getDeptFullPath({0})+'%')", iDeptId);
            sWhere = sWhere == "" ? "" : "and " + sWhere;
            string sSql = string.Format(@"SELECT     a.ID, a.PlanCode, a.PlanType, a.DevID, a.RepairType, a.RepairCyc, a.RepairDanwei, CONVERT(Varchar(10), a.dRepairLastTime, 120) AS dRepairLastTime, CONVERT(Varchar(10), a.dRepairNextTime, 120) AS dRepairNextTime, a.RepairNeed, 
                      a.comment, b.DeviceCode, b.DeviceName, b.Devicespec, c.DeptName
FROM         dbo.Department AS c INNER JOIN
                      dbo.Device AS b ON c.DeptId = b.DeptId RIGHT OUTER JOIN
                      dbo.T_RepairOrBYCycNote AS a ON b.DeviceId = a.DevID
            where 1=1 and PlanType='保养' {0} {1}", sWhere, sDept, iStartRow, iEndRow);
            return db.GetTable(sSql);
        }

        public DataSet getDevMaintainInfoById(int m_Id)
        {
            string sSql = string.Format(@"SELECT     a.ID, a.PlanCode, a.PlanType, a.DevID, a.RepairType, a.RepairCyc, a.RepairDanwei, CONVERT(Varchar(10), a.dRepairLastTime, 120) AS dRepairLastTime, 
                      CONVERT(Varchar(10), a.dRepairNextTime, 120) AS dRepairNextTime, a.RepairNeed, a.bPlanFlag, a.comment, b.DeviceCode, b.DeviceName, b.Devicespec,
                          (SELECT     DeptName
                            FROM          dbo.Department AS c
                            WHERE      (DeptId = b.DeptId)) AS DeptName
FROM         dbo.T_RepairOrBYCycNote AS a INNER JOIN
                      dbo.Device AS b ON a.DevID = b.DeviceId
            where a.ID={0}
            select  * from T_RepairOrBYCycItem where PlanID={0}", m_Id);
            return db.GetDataSetBySql(sSql);
        }

        public bool deleteDevMaintainByIds(string sIds)
        {
            string sSql = string.Format(@"delete from T_RepairOrBYCycNote where ID in({0}) ", sIds);
            return db.ExecuteNonQuery(sSql, true) > 0;
        }

        public bool setMaintainAppInfo(int maintainid, string maintaincode, int devid, string maintaintype, int maintaincyc, string cycunit, string begindate, string enddate, string zhaiyao, string comment)
        {
            string sSql = maintainid == 0 ? string.Format(@"INSERT INTO T_RepairOrBYCycNote
(PlanCode
,PlanType
,DevID
,RepairType
,RepairCyc
,RepairDanwei
,dRepairLastTime
,dRepairNextTime
,RepairNeed
,comment)
     VALUES('{0}','{1}',{2},'{3}',{4},'{5}','{6}','{7}','{8}','{9}') "
                , maintaincode, "保养", devid, maintaintype, maintaincyc, cycunit, begindate, enddate, zhaiyao, comment)
                : string.Format(@"UPDATE T_RepairOrBYCycNote
   SET PlanCode = {0}
      ,RepairType = '{1}'
      ,RepairCyc = {2}
      ,RepairDanwei = '{3}'
      ,dRepairLastTime = '{4}'
      ,dRepairNextTime = '{5}'
      ,RepairNeed = '{6}'
      ,comment = '{7}' where ID={8}"
                , maintaincode, maintaintype, maintaincyc, cycunit, begindate, enddate, zhaiyao, comment, maintainid);

            return db.ExecuteNonQuery(sSql, true) > 0;
        }

        #endregion

        #region //设备维修周期

        public DataTable getRepairCycList(int iPageId, int iDeptId, string sWhere)
        {
            int iStartRow = (iPageId - 1) * iPageRowNum + 1;
            int iEndRow = iPageId * iPageRowNum;
            string sDept = iDeptId == 0 ? @"" : string.Format(" and b.Deptid in(select deptid from department where dbo.getDeptFullPath(deptid) like dbo.getDeptFullPath({0})+'%')", iDeptId);
            sWhere = sWhere == "" ? "" : "and " + sWhere;
            string sSql = string.Format(@"SELECT     a.ID, a.PlanCode, a.PlanType, a.DevID, a.RepairType, a.RepairCyc, a.RepairDanwei, CONVERT(Varchar(10), a.dRepairLastTime, 120) 
                      AS dRepairLastTime, CONVERT(Varchar(10), a.dRepairNextTime, 120) AS dRepairNextTime, a.RepairNeed, a.comment, b.DeviceCode, b.DeviceName, 
                      b.Devicespec, c.DeptName
FROM         dbo.Department AS c INNER JOIN
                      dbo.Device AS b ON c.DeptId = b.DeptId RIGHT OUTER JOIN
                      dbo.T_RepairOrBYCycNote AS a ON b.DeviceId = a.DevID
            where 1=1 and PlanType='检修' {0} {1}", sWhere, sDept, iStartRow, iEndRow);
            return db.GetTable(sSql);
        }

        public DataSet getDevRepairCycInfoById(int m_Id)
        {
            string sSql = string.Format(@"SELECT    a.ID, a.PlanCode, a.PlanType, a.DevID, a.RepairType, a.RepairCyc, a.RepairDanwei, CONVERT(Varchar(10), a.dRepairLastTime, 120) 
                      AS dRepairLastTime, CONVERT(Varchar(10), a.dRepairNextTime, 120) AS dRepairNextTime, a.RepairNeed, a.comment, b.DeviceCode, b.DeviceName, b.Devicespec,(select DeptName from Department as c where c.DeptId=b.DeptId) as DeptName
FROM         dbo.T_RepairOrBYCycNote AS a INNER JOIN
                      dbo.Device AS b ON a.DevID = b.DeviceId
            where a.ID={0}
            select  * from T_RepairOrBYCycItem where PlanID={0}", m_Id);
            return db.GetDataSetBySql(sSql);
        }

        public bool deleteRepairCycByIds(string sIds)
        {
            string sSql = string.Format(@"delete from T_RepairOrBYCycNote where ID in({0}) 
            delete from T_RepairOrBYCycItem where PlanID in({0})", sIds);
            return db.ExecuteNonQuery(sSql, true) > 0;
        }

        public bool setRepairCycInfo(int id, string maintaincode, int devid, string maintaintype, int maintaincyc, string cycunit, string begindate, string enddate, string zhaiyao, string comment, string[] Items)
        {
            string sSql = id == 0 ? string.Format(@"INSERT INTO T_RepairOrBYCycNote
(PlanCode
,PlanType
,DevID
,RepairType
,RepairCyc
,RepairDanwei
,dRepairLastTime
,dRepairNextTime
,RepairNeed
,comment)
     VALUES('{0}','{1}',{2},'{3}',{4},'{5}','{6}','{7}','{8}','{9}') declare @Id int set @Id=@@identity"
                , maintaincode, "检修", devid, maintaintype, maintaincyc, cycunit, begindate, enddate, zhaiyao, comment)
                : string.Format(@"UPDATE T_RepairOrBYCycNote
   SET PlanCode = '{0}'
      ,RepairType = '{1}'
      ,RepairCyc = {2}
      ,RepairDanwei = '{3}'
      ,dRepairLastTime = '{4}'
      ,dRepairNextTime = '{5}'
      ,RepairNeed = '{6}'
      ,comment = '{7}' where ID={8} "
                , maintaincode, maintaintype, maintaincyc, cycunit, begindate, enddate, zhaiyao, comment, id);

            int iLength = Items.Length;
            string sPlanId = id > 0 ? Convert.ToString(id) : "@Id";
            sSql += string.Format(@" delete T_RepairOrBYCycItem where PlanID={0} ", sPlanId);
            for (int i = 0; i < iLength; i = i + 3)
            {
                sSql += string.Format(@" INSERT INTO T_RepairOrBYCycItem (PlanID,ItemName,ItemNeed,Comment) values({0},'{1}','{2}','{3}')"
                , sPlanId, Items[i], Items[i + 1], Items[i + 2]);
            }
            return db.ExecuteNonQuery(sSql, true) > 0;
        }

        #endregion

        #region //生成维修计划，写入维修报告表

        public DataTable getRepairReportList(int iPageId, int iDeptId, string sWhere)
        {
            //int iStartRow = (iPageId - 1) * iPageRowNum + 1;
            //int iEndRow = iPageId * iPageRowNum;
            string sDept = iDeptId == 0 ? @"" : string.Format(" and b.Deptid in(select deptid from department where dbo.getDeptFullPath(deptid) like dbo.getDeptFullPath({0})+'%')", iDeptId);
            sWhere = sWhere == "" ? "" : "and " + sWhere;
            string sSql = string.Format(@"SELECT     a.ID, a.PlanID, a.PlanCode, a.DevID, a.ReportType, CONVERT(Varchar(10), a.dBeginTime, 120) AS dBeginTime, CONVERT(Varchar(10), a.dEndTime, 
                      120) AS dEndTime, a.ActualFeiy, a.ShishiComment, a.ImagePath, a.DevXingneng, a.ProductPinzhi, a.Comment, b.DeviceCode, b.DeviceName, 
                      b.Devicespec, c.DeptName
FROM         dbo.Department AS c INNER JOIN
                      dbo.Device AS b ON c.DeptId = b.DeptId RIGHT OUTER JOIN
                      dbo.T_RepairRecord AS a ON b.DeviceId = a.DevID
            where 1=1 {0} {1}", sWhere, sDept);
            return db.GetTable(sSql);
        }

        public DataSet getRepairReportInfoByPlanId(int m_Id)
        {
            string sSql = string.Format(@"SELECT     a.ID, a.PlanID, a.PlanCode, a.DevID, a.ReportType, CONVERT(Varchar(10), a.dBeginTime, 120) AS dBeginTime, CONVERT(Varchar(10), a.dEndTime, 
                      120) AS dEndTime, a.ActualFeiy, a.ShishiComment, a.ImagePath, a.DevXingneng, a.ProductPinzhi, a.Comment, b.DeviceCode, b.DeviceName, b.Devicespec,(select DeptName from Department as c where c.DeptId=b.DeptId) as DeptName
FROM         dbo.T_RepairRecord AS a INNER JOIN
                      dbo.Device AS b ON a.DevID = b.DeviceId
            where a.ID={0}", m_Id);
            DataSet ds = new DataSet();
            ds = db.GetDataSetBySql(sSql);
            if (ds == null)
            {
                GlobleErrString = db.GlobleErrString;
                return null;
            }
            return ds;
        }

        public bool deleteRepairReportByIds(string sIds)
        {
            string sSql = string.Format(@"delete from T_RepairRecord where ID in({0}) ", sIds);
            return db.ExecuteNonQuery(sSql, true) > 0;
        }

        public DataSet getItemOrPartInfoByPlanId(int iPlanID, string sReportType)
        {
            string sSql = string.Empty;
            if (sReportType == "保养")
            {
                sSql += string.Format(@"select  * from T_RepairOrBYCycItem where PlanID={0}", iPlanID);
            }
            if (sReportType == "维修")
            {
                sSql += string.Format(@"select  * from T_RepairPlanItem where PlanID={0}
            SELECT     a.ID, a.PlanID, a.PartID, a.iNeedNum, a.dPlanTime, b.PartCode, b.PartName, b.Xinghaogg
FROM         dbo.T_RepairPlanPartList AS a INNER JOIN
                      dbo.Table_PartList AS b ON a.PartID = b.ID 
            where PlanID={0}", iPlanID);
            }
            DataSet ds = new DataSet();
            ds = db.GetDataSetBySql(sSql);
            if (ds == null)
            {
                GlobleErrString = db.GlobleErrString;
                return null;
            }
            return ds;
        }

        public bool WriteRepairReport(string sIDList,string sDBTableName, string sReportType)
        {
            string[] sIDCount = sIDList.Split(",".ToCharArray());
            string sSql = string.Empty;
            foreach (string sID in sIDCount)
            {
                string[] iID = sID.Split(";".ToCharArray());
                sSql += string.Format(@" INSERT INTO T_RepairRecord (PlanID, DevID, ReportType) VALUES({0},{1},'{2}') 
                Update " + sDBTableName + " set bPlanFlag=1 where ID={0}"
                , iID[0], iID[1], sReportType);
            }
            //string sSql = string.Format(@"INSERT INTO T_RepairRecord (PlanID, DevID) VALUES({0},{1})"
            //    , maintaincode, "检修", devid, maintaintype, maintaincyc, cycunit, begindate, enddate, zhaiyao, comment);
            int iCount = db.ExecuteNonQuery(sSql, true);
            if (iCount <= 0)
            {
                GlobleErrString = db.GlobleErrString;
                return false;
            }
            return true;
        }

        public bool setRepairReportInfo(int id, string dcode, string BeginDate, string EndDate, string Need, string[] Items, string[] parts, string price, string DevXingneng, string ProductPinzhi, string comment,string porttype)
        {
            string createdate = DateTime.Now.ToString();
            string sSql = string.Format(@"UPDATE T_RepairRecord
   SET PlanCode = {0}
      ,dBeginTime = '{1}'
      ,dEndTime = '{2}'
      ,ActualFeiy = {3}
      ,ShishiComment = '{4}'
      ,DevXingneng = '{5}'
      ,ProductPinzhi = '{6}'
      ,Comment = '{7}' where ID ={8}"
                , dcode, BeginDate, EndDate, price, Need, DevXingneng, ProductPinzhi, comment, id);
            if (porttype == "维修")
            {
                int iLength = Items.Length;
                //string sPlanId = planid > 0 ? Convert.ToString(planid) : "@Id";
                sSql += string.Format(@"delete T_RepairPlanItem where PlanID={0}", id);
                for (int i = 0; i < iLength; i = i + 3)
                {
                    sSql += string.Format(@"INSERT INTO T_RepairPlanItem (PlanID,ItemName,ItemNeed,dPlanTime,comment) values({0},'{1}','{2}','{3}','{4}')"
                    , id, Items[i], Items[i + 1], createdate, Items[i + 2]);
                }

                iLength = parts.Length;
                sSql += string.Format(@"delete T_RepairPlanPartList where PlanID={0}", id);

                for (int j = 0; j < iLength; j = j + 2)
                {
                    sSql += string.Format(@"INSERT INTO T_RepairPlanPartList (PlanID,PartID,iNeedNum,dPlanTime) values({0},{1},{2},'{3}')"
                    , id, parts[j], parts[j + 1], createdate);
                }
            }
            if (porttype == "检修")
            {
                int iLength = Items.Length;
                //string sPlanId = id > 0 ? Convert.ToString(id) : "@Id";
                sSql += string.Format(@" delete T_RepairOrBYCycItem where PlanID={0} ", id);
                for (int i = 0; i < iLength; i = i + 3)
                {
                    sSql += string.Format(@" INSERT INTO T_RepairOrBYCycItem (PlanID,ItemName,ItemNeed,Comment) values({0},'{1}','{2}','{3}')"
                    , id, Items[i], Items[i + 1], Items[i + 2]);
                }
            }
            return db.ExecuteNonQuery(sSql, true) > 0;
        }

        #endregion

        #region //设备作废

        public DataSet getTrashAppInfoById(int iAppId)
        {
            string sSql = string.Format(@"SELECT     a.ID, a.TrashAppCode, CONVERT(Varchar(10), a.TrashAppDate, 120) AS TrashAppDate, a.DevID, a.DeptID, a.TrashPrice, a.TeashReason, a.AvailablePart, 
                      CONVERT(Varchar(10), a.CheckEmploy, 120) AS CheckEmploy, a.CheckDateTime, a.CheckComment, a.bProcessFlag, b.DeviceCode, b.DeviceName, 
                      b.Devicespec,
                          (SELECT     DeptName
                            FROM          dbo.Department AS c
                            WHERE      (DeptId = b.DeptId)) AS DeptName
FROM         dbo.Table_TrashApp AS a INNER JOIN
                      dbo.Device AS b ON a.DevID = b.DeviceId
            where a.ID={0}", iAppId);
            return db.GetDataSetBySql(sSql);
        }

        public DataTable getTrashAppList(int iPageId, int iDeptId, string sWhere)
        {
            //int iStartRow = (iPageId - 1) * iPageRowNum + 1;
            //int iEndRow = iPageId * iPageRowNum;
            string sDept = iDeptId == 0 ? @"" : string.Format(" and b.Deptid in(select deptid from department where dbo.getDeptFullPath(deptid) like dbo.getDeptFullPath({0})+'%')", iDeptId);
            sWhere = sWhere == "" ? "" : "and " + sWhere;
            string sSql = string.Format(@"SELECT     a.ID, a.TrashAppCode, CONVERT(Varchar(10), a.TrashAppDate, 120) AS TrashAppDate, a.DevID, a.DeptID, a.TrashPrice, a.TeashReason, a.AvailablePart, 
                      CONVERT(Varchar(10), a.CheckEmploy, 120) AS CheckEmploy, CONVERT(Varchar(10), a.CheckDateTime, 120) AS CheckDateTime, a.CheckComment, a.bProcessFlag, b.DeviceCode, b.DeviceName, b.Devicespec
FROM         dbo.Table_TrashApp AS a INNER JOIN
                      dbo.Device AS b ON a.DevID = b.DeviceId
            where 1=1 {0} {1}", sWhere, sDept);
            return db.GetTable(sSql);
        }

        public DataTable getTrashProcessList(int iPageId, int iDeptId, string sWhere)
        {
            //int iStartRow = (iPageId - 1) * iPageRowNum + 1;
            //int iEndRow = iPageId * iPageRowNum;
            string sDept = iDeptId == 0 ? @"" : string.Format(" and b.Deptid in(select deptid from department where dbo.getDeptFullPath(deptid) like dbo.getDeptFullPath({0})+'%')", iDeptId);
            sWhere = sWhere == "" ? "" : "and " + sWhere;
            string sSql = string.Format(@"SELECT     b.DeviceCode, b.DeviceName, b.Devicespec, c.Id, c.TrashAppID, c.DevID, Convert(Varchar(10),c.dProcessTime,120) as dProcessTime, c.ProcessType, c.ProcessEmploy, c.ProcessComment, 
                      a.TrashAppCode
FROM         dbo.Table_TrashApp AS a INNER JOIN
                      dbo.Device AS b ON a.DevID = b.DeviceId INNER JOIN
                      dbo.Table_TrashNote AS c ON a.ID = c.TrashAppID
            where 1=1 {0} {1}", sWhere, sDept);
            return db.GetTable(sSql);
        }

        public bool deleteTrashAppByIds(string sIds)
        {
            string sSql = string.Format(@"delete from Table_TrashApp where ID in({0}) 
            delete from Table_TrashNote where TrashAppID in({0})", sIds);
            return db.ExecuteNonQuery(sSql, true) > 0;
        }

        public bool setTrashAppInfo(int cid, int devid, string ccode, string cdate, string trashreason, string usablepart,
            string checkman, string checkdate, string checkcomment, string trashprice, string selectids)
        {
            string sSql = cid == 0 ? string.Format(@"INSERT INTO Table_TrashApp
           (TrashAppCode
           ,TrashAppDate
           ,DevID
           ,TrashPrice
           ,TeashReason
           ,AvailablePart
           ,CheckEmploy
           ,CheckDateTime
           ,CheckComment)
     VALUES
           ('{0}'
           ,'{1}'
           ,{2}
           ,{3}
           ,'{4}'
           ,'{5}'
           ,'{6}'
           ,'{7}'
           ,'{8}')"
                , ccode, cdate, devid, trashprice, trashreason, usablepart, checkman, checkdate, checkcomment)
                : string.Format(@"UPDATE Table_TrashApp
   SET TrashPrice = '{0}'
      ,TeashReason = '{1}'
      ,AvailablePart = '{2}'
      ,CheckEmploy = '{3}'
      ,CheckDateTime = '{4}'
      ,CheckComment = '{5}' where ID={6} "
                , trashprice, trashreason, usablepart, checkman, checkdate, checkcomment, cid);
            //int iLength = sDevices.Length;
            //string sContractId = iContractId > 0 ? Convert.ToString(iContractId) : "@Id";
            //for (int i = 0; i < iLength; i = i + 4)
            //{
            //    sSql += string.Format(@"insert into Devicecontract(ContractId,typeid,devicenum,singleprice,modelinfo) values({0},'{1}','{2}','{3}','{4}')"
            //    , sContractId, sDevices[i], sDevices[i + 1], sDevices[i + 2], sDevices[i + 3]);
            //}
            return db.ExecuteNonQuery(sSql, true) > 0;
        }

        public bool setTrashProcessInfo(ArrayList sIDMulti)
        {
            string sSql = string.Empty;

            for (int i = 0; i < sIDMulti.Count; i++)
            {
                string[] sIDList = sIDMulti[i].ToString().Split(";".ToCharArray());
                sSql += string.Format(@" INSERT INTO Table_TrashNote (TrashAppID ,DevID) VALUES ({0},{1}) ",sIDList[0],sIDList[1]);
            }

            return db.ExecuteNonQuery(sSql, true) > 0;
        }

        public DataSet getTrashNoteInfoById(int iNoteId)
        {
            string sSql = string.Format(@"SELECT a.Id, a.TrashAppID, a.DevID, Convert(Varchar(10),a.dProcessTime,120) as dProcessTime, a.ProcessType, a.ProcessEmploy, a.ProcessComment, b.TrashAppCode, c.DeviceCode, 
                      c.DeviceName
FROM         dbo.Table_TrashNote AS a INNER JOIN
                      dbo.Table_TrashApp AS b ON a.TrashAppID = b.ID INNER JOIN
                      dbo.Device AS c ON b.DevID = c.DeviceId where a.ID={0}", iNoteId);
            return db.GetDataSetBySql(sSql);
        }

        public bool setTrashNoteInfo(int cid, int devid, string cdate, string processtype, string processman, string processcomment, string selectids)
        {
            string sSql = string.Format(@"UPDATE Table_TrashNote
   SET dProcessTime = '{0}'
      ,ProcessType = '{1}'
      ,ProcessEmploy = '{2}'
      ,ProcessComment = '{3}' where ID={4} 
        UPDATE Device SET DeviceState='报废' where DeviceId={5} "
                , cdate, processtype, processman, processcomment, cid, devid);
            
            return db.ExecuteNonQuery(sSql, true) > 0;
        }

        public bool deleteTrashNoteByIds(string sIds)
        {
            string sSql = string.Format(@"delete from Table_TrashNote where ID in({0}) ", sIds);
            return db.ExecuteNonQuery(sSql, true) > 0;
        }

        #endregion


        #region //设备故障列表

        public DataTable getTroubleList(int iPageId, int iDeptId, string sWhere)
        {
            //int iStartRow = (iPageId - 1) * iPageRowNum + 1;
            //int iEndRow = iPageId * iPageRowNum;
            string sDept = iDeptId == 0 ? @"" : string.Format(" and b.Deptid in(select deptid from department where dbo.getDeptFullPath(deptid) like dbo.getDeptFullPath({0})+'%')", iDeptId);
            sWhere = sWhere == "" ? "" : "and " + sWhere;
            string sSql = string.Format(@"SELECT     a.ID, a.TroubleCode, a.DevID, a.TroubleCom, a.ProcessWay, a.TroubleReason, Convert(Varchar(10),a.HappenTime,120) as HappenTime, Convert(Varchar(10),a.RepairTime,120) as RepairTime, a.NeedMoney, a.TroubleType, 
                      a.AddNoteMan, a.YingXiang, a.ProcessDept, b.DeviceCode, b.DeviceName, b.Devicespec, c.DeptName
FROM         dbo.Department AS c INNER JOIN
                      dbo.Device AS b ON c.DeptId = b.DeptId RIGHT OUTER JOIN
                      dbo.T_TroubleNoteList AS a ON b.DeviceId = a.DevID
            where 1=1 {0} {1}", sWhere, sDept);
            return db.GetTable(sSql);
        }

        public DataSet getDevTroubleInfoById(int m_Id)
        {
            string sSql = string.Format(@"SELECT     a.ID, a.TroubleCode, a.DevID, a.TroubleCom, a.ProcessWay, a.TroubleReason, Convert(Varchar(10),a.HappenTime,120) as HappenTime, Convert(Varchar(10),a.RepairTime,120) as RepairTime, a.NeedMoney, a.TroubleType, 
                      a.AddNoteMan, a.YingXiang, a.ProcessDept, b.DeviceCode, b.DeviceName, b.Devicespec,(select DeptName from Department as c where c.DeptId=b.DeptId) as DeptName
FROM         dbo.T_TroubleNoteList AS a INNER JOIN
                      dbo.Device AS b ON a.DevID = b.DeviceId
            where a.ID={0}
            SELECT     a.ID, a.TroubleID, a.PartID, a.iNeedNum, b.PartCode, b.PartName, b.Xinghaogg
FROM         dbo.T_TroubleChangePartList AS a INNER JOIN
                      dbo.Table_PartList AS b ON a.PartID = b.ID where TroubleID={0}", m_Id);
            return db.GetDataSetBySql(sSql);
        }

        public bool deleteDevTroubleByIds(string sIds)
        {
            string sSql = string.Format(@"delete from T_TroubleNoteList where ID in({0}) 
            delete from T_TroubleChangePartList where PartID in({0})", sIds);
            return db.ExecuteNonQuery(sSql, true) > 0;
        }

        public bool setTroubleInfo(int id, int devid, string TroubleCode, string TroubleCom, string ProcessWay, string TroubleReason, string HappenTime, string RepairTime, string NeedMoney, string TroubleType, string AddNoteMan, string YingXiang, string ProcessDept, string[] parts)
        {
            string sSql = id == 0 ? string.Format(@" INSERT INTO T_TroubleNoteList
           (TroubleCode,DevID,TroubleCom,ProcessWay,TroubleReason,HappenTime,RepairTime,NeedMoney,TroubleType,AddNoteMan,YingXiang,ProcessDept) VALUES('{0}',{1},'{2}','{3}','{4}','{5}','{6}',{7},'{8}','{9}','{10}','{11}')  declare @Id int set @Id=@@identity "
                , TroubleCode, devid, TroubleCom, ProcessWay, TroubleReason, HappenTime, RepairTime, NeedMoney, TroubleType, AddNoteMan, YingXiang, ProcessDept)
                : string.Format(@" update T_TroubleNoteList set TroubleCom ='{0}',ProcessWay = '{1}',TroubleReason = '{2}',HappenTime ='{3}',RepairTime ='{4}',NeedMoney ='{5}',TroubleType = '{6}',AddNoteMan ='{7}',YingXiang ='{8}',ProcessDept ='{9}' where ID={10} "
                , TroubleCom, ProcessWay, TroubleReason, HappenTime, RepairTime, NeedMoney, TroubleType, AddNoteMan, YingXiang, ProcessDept, id);
            string sPlanId = id > 0 ? Convert.ToString(id) : "@Id";
            int iLength = parts.Length;
            sSql += string.Format(@"delete T_TroubleChangePartList where TroubleID={0}", sPlanId);

            for (int j = 0; j < iLength; j = j + 2)
            {
                sSql += string.Format(@" INSERT INTO T_TroubleChangePartList (TroubleID,PartID,iNeedNum) values({0},{1},{2}) "
                , sPlanId, parts[j], parts[j + 1]);
            }
            return db.ExecuteNonQuery(sSql, true) > 0;
        }

        #endregion
    }
}
